package com.headsen.springboot.excel.service;

import com.headsen.springboot.excel.controller.model.ExamImport;
import com.headsen.springboot.excel.controller.model.ExcelData;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel解析填充
 *
 * @author
 */
public class ExcelService {

    public static ExcelData buildTestData() {
        ExcelData data = ExcelData.builder().sendDate(new Date()).collegeName("软件学院").courseName("算法设计与分析(双语)")
                .printNum("11*2").examDate(new Date())
                .build();

        List<ExcelData.RowItem> itemList = new ArrayList<>(5);
        for (int i = 1; i < 6; i++) {
            ExcelData.RowItem item = ExcelData.RowItem.builder().no(i).className("xxx" + i).num(i * 2).build();
            itemList.add(item);
        }
        data.setItemList(itemList);
        return data;
    }

    public static XSSFWorkbook scanTemplateAndFill(ExcelData data) throws Exception {
        ClassPathResource classPathResource = new ClassPathResource("template/printing.xlsx");
        XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(classPathResource.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        //数据行数  模板含有一个空行
        int dataRow = data.getRows();

        //数据超过1行，调整模板：最后一行 下移dataRow-1行，插入dataRow-1行
        if (dataRow > 1) {
            sheet.shiftRows(sheet.getLastRowNum(), sheet.getLastRowNum(), dataRow - 1);
            CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
            for (int i = 7; i <= sheet.getLastRowNum() - 1; i++) {
                //第1数据行复制到新加入的行   i=6,j=0,1.0 ,{AI18,大数据19.[1,2],软工17.1,软工18.2,软工18.3，软工19.[1,2,3]}
                sheet.copyRows(6, 6, i, cellCopyPolicy);
            }
        }

        //循环所有单元格，按需填充数据
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                String oldV = cell.toString();
                fillCell(cell, data);
                String newV = cell.toString();
                System.out.println("i=" + i + ",j=" + j + ",old:" + oldV + ",new:" + newV);
            }
        }

        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("D://workbook2.xlsx")) {
            wb.write(fileOut);
        }

        return wb;
    }

    public static List<ExamImport> readImportExam() throws IOException, ParseException {
        ClassPathResource classPathResource = new ClassPathResource("template/import_exam.xls");
        Workbook wb = WorkbookFactory.create(classPathResource.getInputStream());
        Sheet sheet = wb.getSheetAt(0);

//        HSSFWorkbook XSSFWorkbook

        List<ExamImport> exams = new ArrayList<>();
        ExamImport last = null;
        //逐行解析
        for (int i = 0; i <= sheet.getLastRowNum() - 1; i++) {
            Row row = sheet.getRow(i);
            short cellNum = row.getLastCellNum();
            if (cellNum == -1) {
                continue;
            }
//            System.out.println("r=" + i + ",c=" + cellNum);
            //这是表头，
            if (row.getCell(0) != null && row.getCell(0).toString().startsWith("山东大学")) {
                Row titleRow1 = sheet.getRow(i);
                Row titleRow2 = sheet.getRow(++i);
                Row titleRow3 = sheet.getRow(++i);

                String title = titleRow1.getCell(0).toString();
                //考试时间:2023-02-14 14:30~2023-02-14 16:30
                String dateStr = titleRow2.getCell(0).toString();
                Date startTime = new SimpleDateFormat("yyyy-MM-dd HH:mm").parse(dateStr.substring(5, 21));
                Date endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm").parse(dateStr.substring(22, 38));
                String room = titleRow2.getCell(7).toString();
                String num = titleRow2.getCell(10).toString();
                String course = titleRow3.getCell(0).toString();
                String clsName = titleRow3.getCell(8).toString();

                ExamImport examImport = ExamImport.builder()
                        .title(title).startTime(startTime).endTime(endTime)
                        .room(room.substring(3)).num(Integer.valueOf(num.substring(5)))
                        .course(course.substring(6, course.length() - 1)).clsName(clsName).examineeList(new ArrayList<>())
                        .build();
                exams.add(examImport);
                last = examImport;
                i++;
            } else {
                if (row.getCell(1) == null) {
                    break;
                }
                String code1 = row.getCell(1).toString();
                String name1 = row.getCell(2).toString();
                if (code1.length() > 0) {
                    ExamImport.Examinee u1 = ExamImport.Examinee.builder().code(code1).name(name1).build();
                    last.getExamineeList().add(u1);
                }
                if (row.getCell(10) != null && row.getCell(10).toString().length() > 0) {
                    String code2 = row.getCell(9).toString();
                    String name2 = row.getCell(10).toString();
                    ExamImport.Examinee u2 = ExamImport.Examinee.builder().code(code2).name(name2).build();
                    last.getExamineeList().add(u2);
                }
            }
        }
        System.out.println(exams);
        return exams;
    }

    public static Workbook exportExam() throws IOException, ParseException {
        //导出第一场考试
        ExamImport examImport = readImportExam().get(0);
        //模板是xls 2003
        ClassPathResource classPathResource = new ClassPathResource("template/export_exam.xls");
        Workbook wb = WorkbookFactory.create(classPathResource.getInputStream());
        Sheet sheet = wb.getSheetAt(0);

        //打印表头
        Row row0 = sheet.getRow(0);
        Row row1 = sheet.getRow(1);
        Cell cell01 = row0.getCell(0);
        String rc1 = String.format(cell01.toString(), examImport.getCourse(), examImport.getTeacher(), examImport.getClsName());
        cell01.setCellValue(rc1);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Cell cell11 = row1.getCell(0);
        String rc2 = String.format(cell11.toString(), dateFormat.format(examImport.getStartTime()) + "-" + dateFormat.format(examImport.getEndTime())
                , examImport.getRoom(), examImport.getNum().toString());
        cell11.setCellValue(rc2);


        List<ExamImport.Examinee> examineeList = examImport.getExamineeList();
        int seq = 0;
        // 从讲台倒着排位
        for (int i = sheet.getLastRowNum() - 4; i >= 6; i--) {
            Row row = sheet.getRow(i);
            for (int j = row.getLastCellNum(); j > row.getFirstCellNum(); j--) {
                if (seq > examineeList.size()-1) {
                    break;
                }
                if (j % 2 == 0 || row.getCell(j) == null) {
                    continue;
                }
                Cell cell = row.getCell(j);
                String oldV = cell.toString();
                ExamImport.Examinee examinee = examineeList.get(seq);
                cell.setCellValue(examinee.getName() + "\n" + examinee.getCode());
                System.out.println("i=" + i + ",j=" + j + ",old:" + oldV + ",new:" + null);
                seq++;

            }
        }

        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("D://workbook2.xls")) {
            wb.write(fileOut);
        }

        return wb;
    }

    /**
     * 填充单元格
     *
     * @param cell
     */
    private static void fillCell(Cell cell, ExcelData data) {
        int rowNum = cell.getRow().getRowNum();
        int columnIndex = cell.getColumnIndex();
        int rows = data.getRows();
        //数据行
        if (rows > 0 && rowNum >= 6 && rowNum < 6 + rows) {
            int index = rowNum - 6;
            if (columnIndex >= 5) {
                index = index + rows;
            }
            if (data.getItemList().size() > index) {
                ExcelData.RowItem rowItem = data.getItemList().get(index);
                if (columnIndex == 0 || columnIndex == 5) {
                    cell.setCellValue(rowItem.getNo());
                }
                if (columnIndex == 1 || columnIndex == 6) {
                    cell.setCellValue(rowItem.getClassName());
                }
                if (columnIndex == 4 || columnIndex == 9) {
                    cell.setCellValue(rowItem.getNum());
                }
            } else {
                cell.setCellValue("");
            }
        }
        // 送印时间
        if (rowNum == 1 && columnIndex == 5) {
            SimpleDateFormat sf = new SimpleDateFormat("yyyy年MM月dd日");
            String dateS = String.format(cell.getStringCellValue(), sf.format(data.getSendDate()));
            cell.setCellValue(dateS);
        }
        // 院部
        if (rowNum == 2 && columnIndex == 0) {
            String dateS = String.format(cell.getStringCellValue(), data.getCollegeName());
            cell.setCellValue(dateS);
        }
        // 课程
        if (rowNum == 3 && columnIndex == 4) {
            cell.setCellValue(data.getCourseName());
        }
        // 印数
        if (rowNum == 4 && columnIndex == 7) {
            cell.setCellValue(data.getPrintNum());
        }
        // 考试时间
        if (rowNum == 4 && columnIndex == 8) {
            SimpleDateFormat sf = new SimpleDateFormat("MM.dd");
            String dateS = String.format(cell.getStringCellValue(), sf.format(data.getExamDate()));
            cell.setCellValue(dateS);
        }

//        CellType cellType = cell.getCellType();
    }
}
